import hashlib
import os

from flask import Flask, render_template, request, jsonify, redirect, url_for
from flask_redis import FlaskRedis
from flask_sqlalchemy import SQLAlchemy
from flask_mail import Mail, Message
import config
import json
import time
import random
import myfilter

app = Flask(__name__)
# 导入配置
app.config.from_object(config)
db = SQLAlchemy(app)
redis_client = FlaskRedis()
redis_client.init_app(app)
# 加载自定义过滤器
app.add_template_filter(myfilter.sex)
# 创建Mail对象
mail = Mail(app)


# 定义测试题数据表
class Quizzes(db.Model):
    __tablename__ = "quizzes"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)  # id
    quizzes = db.Column(db.String(255))  # 题目


# 定义测试题问卷数据表
class Questionnaire(db.Model):
    __tablname__ = 'questionnaire'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)  # id
    title = db.Column(db.String(255))  # 标题
    tips = db.Column(db.Text)  # 提示
    options = db.Column(db.Text)  # 选项
    result = db.Column(db.Text)  # 结果
    quizzes = db.Column(db.Text)  # 测试题ID


# 用户表
class User(db.Model):
    __tablname__ = 'users'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)  # ID
    name = db.Column(db.String(255))  # 名称
    email = db.Column(db.String(255))  # 邮箱
    pwd = db.Column(db.String(255))  # 密码
    head = db.Column(db.String(255), default='/static/images/head.jpg')  # 头像
    data = db.Column(db.Text)  # 测试数据
    sex = db.Column(db.Boolean, nullable=True)  # 性别
    age = db.Column(db.Integer, default=16)  # 年龄
    last_login = db.Column(db.String(30))  # 最近登录


# 平台记录
class PlatformLog(db.Model):
    __tablname__ = 'platform_log'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)  # ID
    date = db.Column(db.String(30))  # 日期
    log = db.Column(db.Text)  # 记录


# 检查数据库中是否存在数据表，如果不存在则创建
db.create_all()


# 设置验证码缓存
def set_redis_data(key, data):
    key = str(key).encode('utf-8')
    data = str(data).encode('utf-8')
    expires = int(time.time()) + 300
    p = redis_client.pipeline()
    p.set(key, data)
    p.expireat(key, expires)
    p.execute()


# 获取验证码缓存
def get_redis_data(key):
    key = str(key).encode('utf-8')
    data = redis_client.get(key)
    return data.decode('utf-8')


# 邮箱6位数字验证码
def get_auth_code():
    codes = [i.decode() for i in redis_client.keys()]
    print(codes)
    while True:
        code = random.randint(100000, 999999)  # 随机生成6位数字
        if code not in codes:  # 判断验证码是否存在，已存在则重新生成
            return code


# 校验邮箱
def judge_email(code, email):
    codes = [i.decode() for i in redis_client.keys()]
    if code in codes:
        e = get_redis_data(code)
        if e == email:
            return True
        else:
            return False
    else:
        return False


# 格式化时间
def formatTime(timestamp='', date=False):
    if timestamp:
        return time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(timestamp))
    else:
        if date:
            return time.strftime("%Y-%m-%d")
        else:
            return time.strftime("%Y-%m-%d %H:%M:%S")


# flaks中间件
@app.before_request
def process():
    urls = [
        '/',
        '/tests'
    ]
    cms_urls = [
        '/cms',
        '/user_control',
        '/question_bank'
    ]
    # 平台记录请求次数
    date = PlatformLog.query.filter(PlatformLog.date == formatTime(date=True)).first()
    if date:
        log = json.loads(date.log)
        log['visit'] += 1
        date.log = json.dumps(log)
        db.session.commit()
    else:
        db.session.add(PlatformLog(date=formatTime(date=True), log=json.dumps({"register": 0, "visit": 1, "test": 0})))
        db.session.commit()
    # 用户登录鉴权
    if request.path in urls:
        uid = request.cookies.get('uid')
        if not User.query.filter(User.id == uid).first():
            return redirect(url_for('LoginView'))
    # 管理员登录鉴权
    if request.path in cms_urls:
        secret_key = request.cookies.get('secret_key')
        if secret_key != config.SECRET_KEY:
            return redirect(url_for('AdminView'))


# 用户登录
@app.route('/login', methods=['GET', 'POST'])
def LoginView():
    if request.method == 'GET':  # 返回登录页面
        return render_template('login.html')
    elif request.method == 'POST':
        action = request.form.get('action')  # 操作
        email = request.form.get('email')  # 邮箱
        if action == 'auth_code':  # 获取验证码
            try:
                auth_code = get_auth_code()
                # 参数分别是 邮件标题 发送账号 接收方(可以有多个)
                msg = Message("老年人心理健康评估", sender='2501752219@qq.com', recipients=[email])
                # 邮件体内容
                msg.body = f'你好,您的验证码为:{auth_code},5分钟内有效。'
                # 发送邮件
                mail.send(msg)
                set_redis_data(auth_code, email)
                return jsonify({'state': True})
            except:
                return jsonify({'state': False})
        elif action == 'login':  # 登录
            pwd = request.form.get('pwd')  # 密码
            user = User.query.filter(User.email == email).first()  # 根据邮箱查找用户信息
            if user:  # 判断用户是否存在
                if user.pwd == pwd:  # 判断密码是否正确
                    user.last_login = formatTime()  # 记录登录时间
                    db.session.commit()  # 提交数据库
                    return jsonify({'state': True, 'uid': user.id})  # 返回状态和uid
                else:
                    return jsonify({'state': False, 'msg': '密码错误！'})
            else:
                return jsonify({'state': False, 'msg': '用户不存在！'})
        elif action == 'register':  # 注册
            name = request.form.get('name')  # 名称
            pwd = request.form.get('pwd')  # 密码
            code = request.form.get('code')  # 验证码
            if User.query.filter(User.email == email).first():  # 判断邮箱是否注册
                return jsonify({'state': False, 'msg': '此邮箱已注册！'})
            if judge_email(code, email):  # 判断邮箱和验证码是否匹配
                data = {
                    i.id: {'id': i.id, 'title': i.title, 'state': False, 'time': '', 'result': {}, 'tips': '', 'lv': ''}
                    for i in Questionnaire.query.all()}
                db.session.add(
                    User(name=name, email=email, pwd=pwd, data=json.dumps(data, ensure_ascii=False),
                         last_login=formatTime()))  # 往数据库中添加数据
                date = PlatformLog.query.filter(PlatformLog.date == formatTime(date=True)).first()
                log = json.loads(date.log)
                log['register'] += 1
                date.log = json.dumps(log)
                db.session.commit()  # 提交执行
                return jsonify({'state': True})
            else:
                return jsonify({'state': False, 'msg': '验证码错误！'})
        elif action == 'forget':  # 忘记密码
            user = User.query.filter(User.email == email).first()
            if user:
                pass
                try:
                    # 参数分别是 邮件标题 发送账号 接收方(可以有多个)
                    msg = Message("老年人心理健康评估", sender='2870093961@qq.com', recipients=[email])
                    # 邮件体内容
                    msg.body = f'你好,您的密码为为:{user.pwd},登陆后请及时修改。'
                    # 发送邮件
                    mail.send(msg)
                    return jsonify({'state': True})
                except:
                    return jsonify({'state': False, 'msg': '邮件发送失败'})
            else:
                return jsonify({'state': False, 'msg': '没有此用户'})


# 用户首页
@app.route('/', methods=['GET', 'POST'])
def UserView():
    if request.method == 'GET':
        uid = request.cookies.get('uid')  # 从cookies获取uid
        user = User.query.filter(User.id == uid).first()  # 从数据库中提取用户数据
        userData = json.loads(user.data)  # 用户数据str装json
        context = {
            "quizzes": [],
            "id": user.id,
            "head": user.head,
            "name": user.name,
            "email": user.email,
            "sex": user.sex,
            "age": user.age
        }
        for i in Questionnaire.query.all():  # 遍历问卷
            d = {
                'id': i.id,
                "title": i.title,
                "tips": i.tips,
            }
            if str(i.id) in userData:  # 判断是否有缺失数据
                d["state"] = userData[str(i.id)]['state']
                d["time"] = userData[str(i.id)]['time']
                d["result"] = userData[str(i.id)]['result']
            else:  # 补全数据
                d["state"] = False
                d["time"] = ''
                userData[i.id] = {'id': i.id, 'title': i.title, 'state': False, 'time': '', 'result': {}}
            context['quizzes'].append(d)
        context['quizzes'] = json.dumps(context['quizzes'], ensure_ascii=False)
        user.save()
        return render_template('index.html', context=context)
    elif request.method == 'POST':
        action = request.form.get('action')  # 操作
        uid = request.cookies.get('uid')
        user = User.query.filter(User.id == uid).first()
        if action == 'sex':  # 修改性别
            try:
                sex = request.form.get('sex')
                user.sex = sex = True if sex == 'true' else False
                db.session.commit()
                return jsonify({'state': True})
            except:
                return jsonify({'state': False})
        elif action == 'age':  # 修改年龄
            try:
                age = request.form.get('age')
                user.age = int(age)
                db.session.commit()
                return jsonify({'state': True})
            except:
                return jsonify({'state': False})
        elif action == 'pwd':  # 修改密码
            try:
                pwd = request.form.get('pwd')
                user.pwd = pwd
                db.session.commit()
                return jsonify({'state': True})
            except:
                return jsonify({'state': False})
        elif action == 'name':  # 修改名称
            try:
                name = request.form.get('name')
                user.name = name
                db.session.commit()
                return jsonify({'state': True, 'name': name})
            except:
                return jsonify({'state': False})


# 测试问卷
@app.route('/tests', methods=['GET', 'POST'])
def TestsView():
    if request.method == 'GET':
        tests_id = int(request.args.get('id'))  # 问卷ID
        q = Questionnaire.query.filter(Questionnaire.id == tests_id).first()  # 查询问卷信息
        context = {
            "id": q.id,
            "title": q.title,
            "tips": q.tips,
            "options": json.loads(q.options),
            "result": json.loads(q.result),
            "quizzes": [],
        }
        for i in json.loads(q.quizzes):  # 从数据库中提取问卷包含的测试题
            data = {
                "title": i['title'],
                "quizzes": [],
            }
            for j in i['quizzes']:
                data['quizzes'].append({
                    'title': Quizzes.query.get(j).quizzes,
                    'value': 1
                })
            context['quizzes'].append(data)
        return render_template(f'tests{tests_id}.html', context=context)
    elif request.method == 'POST':
        uid = request.cookies.get('uid')  # 用户ID
        tid = request.form.get('tid')  # 问卷ID
        result = json.loads(request.form.get('result'))  # 测试结果数据
        tips = request.form.get('tips')  # 提示
        lv = request.form.get('lv')  # 状态等级颜色(用于后台提示)
        user = User.query.filter(User.id == uid).first()  # 用户数据
        data = json.loads(user.data)
        q = Questionnaire.query.filter(Questionnaire.id == tid).first()
        data[tid] = {'id': int(tid), 'title': q.title, 'state': True, 'time': formatTime(), 'result': result,
                     'tips': tips, 'lv': lv}  # 保存结果到用户数据
        user.data = json.dumps(data, ensure_ascii=False)
        date = PlatformLog.query.filter(PlatformLog.date == formatTime(date=True)).first()
        log = json.loads(date.log)
        log['test'] += 1
        date.log = json.dumps(log)
        db.session.commit()  # 提交数据库
        return jsonify({})


# 管理员登录
@app.route('/admin', methods=['GET', 'POST'])
def AdminView():
    if request.method == 'GET':
        return render_template('admin.html')
    elif request.method == 'POST':
        name = request.form.get('name')  # 用户名
        pwd = request.form.get('pwd')  # 密码
        if name != config.USERNAME:  # 判断用户名
            return jsonify({'state': False, 'msg': '用户名错误'})
        if pwd != config.PASSWORD:  # 判断密码
            return jsonify({'state': False, 'msg': '密码错误'})
        s = request.remote_addr + str(time.time())
        config.SECRET_KEY = hashlib.md5(s.encode(encoding='UTF-8')).hexdigest()  # MD5加密生成管理员密钥
        return jsonify({'state': True, 'secret_key': config.SECRET_KEY})  # 把密钥返回给浏览器


# 后台管理首页
@app.route('/cms', methods=['GET', 'POST'])
def CMSView():
    context = {
        "date": [],
        "register": [],
        "visit": [],
        "test": [],
        "register_count": 0,
        "visit_count": 0,
        "test_count": 0
    }
    for i in PlatformLog.query.all():  # 从数据库提取平台记录
        log = json.loads(i.log)
        context['date'].append(i.date)  # 日期
        context['register'].append(log['register'])  # 注册人数
        context['visit'].append(log['visit'])  # 请求次数
        context['test'].append(log['test'])  # 请求次数
        context['register_count'] += log['register']  # 总注册人数
        context['visit_count'] += log['visit']  # 总请求次数
        context['test_count'] += log['test']  # 总请求次数

    return render_template('cms.html', context=context)


# 用户管理
@app.route('/user_control', methods=['GET', 'POST'])
def UserControlView():
    if request.method == 'GET':
        users = []
        for i in User.query.all():  # 获取所有用户
            data = {
                "id": i.id,
                "name": i.name,
                "pwd": i.pwd,
                "email": i.email,
                "sex": myfilter.sex2(i.sex),
                "age": i.age,
                "last_login": i.last_login,
                "data": [i for i in json.loads(i.data).values()]
            }
            users.append(data)
        users = json.dumps(users, ensure_ascii=False)
        return render_template('user_control.html', users=users)
    elif request.method == 'POST':
        action = request.form.get('action')  # 操作
        uid = request.form.get('uid')  # 用户ID
        if action == 'del':  # 删除用户
            try:
                user = User.query.filter(User.id == uid).first()
                db.session.delete(user)
                db.session.commit()
                users = []
                for i in User.query.all():
                    data = {
                        "id": i.id,
                        "name": i.name,
                        "pwd": i.pwd,
                        "email": i.email,
                        "sex": myfilter.sex2(i.sex),
                        "age": i.age,
                        "last_login": i.last_login,
                        "data": [i for i in json.loads(i.data).values()]
                    }
                    users.append(data)
                return jsonify({'state': True, 'users': users})
            except:
                return jsonify({'state': False})
        elif action == 'alter':  # 修改用户信息
            try:
                data = json.loads(request.form.get('data'))  # 获取前端提交的数据
                user = User.query.filter(User.id == data['id']).first()
                user.name = data['name']
                user.pwd = data['pwd']
                user.email = data['email']
                user.age = data['age']
                if data['sex']:
                    user.sex = True if data['sex'] == '男' else False
                db.session.commit()  # 保存到数据库
                users = []
                for i in User.query.all():
                    data = {
                        "id": i.id,
                        "name": i.name,
                        "pwd": i.pwd,
                        "email": i.email,
                        "sex": myfilter.sex2(i.sex),
                        "age": i.age,
                        "last_login": i.last_login,
                        "data": [i for i in json.loads(i.data).values()]
                    }
                    users.append(data)
                return jsonify({'state': True, 'users': users})
            except:
                return jsonify({'state': False})


# 题库管理
@app.route('/question_bank', methods=['GET', 'POST'])
def QuestionBankView():
    def tk():  # 获取题库数据
        result = []
        for q in Questionnaire.query.all():  # 问卷
            t = {
                "id": q.id,
                "title": q.title,
                "tips": q.tips,
                "options": json.loads(q.options),
                "resutl": json.loads(q.result),
                "quizzes": []
            }
            for i in json.loads(q.quizzes):  # 测试题
                data = {
                    "title": i['title'],
                    "quizzes": [],
                }
                for j in i['quizzes']:
                    d = Quizzes.query.get(j)
                    data['quizzes'].append({
                        'id': d.id,
                        'title': d.quizzes,
                        'value': 1
                    })
                t['quizzes'].append(data)
            result.append(t)
        return result

    if request.method == 'GET':
        context = {
            "q": tk()
        }
        return render_template('question_bank.html', context=context)
    elif request.method == 'POST':
        action = request.form.get('action')  # 操作
        tid = request.form.get('tid')  # 问卷ID/测试题ID
        index = request.form.get('index')  # 测试题索引
        title = request.form.get('title')  # 题目
        if action == 'alter':  # 修改
            try:
                t = Quizzes.query.filter(Quizzes.id == tid).first()  # 获取测试题
                t.quizzes = title  # 修改
                db.session.commit()  # 提交数据库
                return jsonify({'state': True, 'data': tk()})
            except:
                return jsonify({'state': False})
        elif action == 'add':  # 添加
            try:
                db.session.add(Quizzes(quizzes=title))  # 添加到题库
                db.session.commit()
                q = Questionnaire.query.filter(Questionnaire.id == tid).first()
                items = json.loads(q.quizzes)
                items[int(index)]['quizzes'].append(Quizzes.query.all()[-1].id)  # 更新问卷
                q.quizzes = json.dumps(items, ensure_ascii=False)
                db.session.commit()  # 提交到数据库
                return jsonify({'state': True, 'data': tk()})
            except:
                return jsonify({'state': False})
        elif action == 'del':  # 删除
            try:
                # 删除题库
                qid = request.form.get('qid')
                t = Questionnaire.query.filter(Questionnaire.id == tid).first()
                items = json.loads(t.quizzes)
                items[int(index)]['quizzes'].remove(int(qid))
                t.quizzes = json.dumps(items, ensure_ascii=False)
                # 更新问卷数据
                q = Quizzes.query.filter(Quizzes.id == qid).first()
                db.session.delete(q)
                db.session.commit()
                return jsonify({'state': True, 'data': tk()})
            except:
                return jsonify({'state': False})


# 头像上传
@app.route('/up_head', methods=['post'])
def UplaodHead():
    try:
        basedir = os.path.abspath(os.path.dirname(__file__))  # 获取文件的绝对位置
        img = request.files.get('file')  # 文件对象
        uid = request.form.get('uid')  # 用户ID
        file_path = '/static/images/' + str(int(time.time() * 100)) + '.jpg'  # 文件存储路径
        img.save(basedir + file_path)  # 保存文件
        User.query.filter(User.id == uid).first().head = file_path  # 更新用户数据
        db.session.commit()
        return jsonify({'state': True})
    except:
        return jsonify({'state': False})


if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port=5000)  # 运行
